let server = require("express")();
let expressStatic = require("express-static");
let port = 81;

let mysql = require("mysql");

let sql = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '123456',
    database : 'news',
    timezone : "08:00"
  });
  
// 尝试链接
sql.connect();
//获取数据库
server.get("/getStudentList",(request,response)=>{
    sql.query(`SELECT * FROM student`,(error,data)=>{
		response.send(data)
	})
})
//删除数据库中元素
server.get("/deleteStudent",(request,response)=>{
    sql.query(`DELETE FROM student WHERE id=${request.query.id}`,(error,data)=>{
        if(!error){
            console.log("删除成功")
            response.send("success")
        }
    })
})
//给数据库添加元素
server.get("/addStudent",(request,response)=>{
    // console.log(8)
    let p = request.query
    sql.query(`INSERT INTO student (name,age,sex,city,joinDate) VALUES ("${p.name}","${p.age}","${p.sex}","${p.city}","${p.joinDate}")`,(error,data)=>{
        if(!error){
            console.log(`${p.name}添加成功`)
            response.send("success")
        }else{
            console.log(error)
        }
    })
})
//修改数据库元素
server.get("/editStudent",(request,response)=>{
    let p = request.query;
    sql.query(`UPDATE student SET name="${p.name}",age="${p.age}",sex="${p.sex}",city="${p.city}",joinDate="${p.joinDate}" WHERE id="${p.id}"`,(error,data)=>{
        if(!error){
            console.log(`${p.name}修改成功`)
            response.send("success")
        }else{
            console.log(error)
        }
    })
})
server.use(expressStatic(__dirname + '/manage'));
console.log(`running at ${port}`)
server.listen(port)